from flask import Flask, request, send_file, send_from_directory
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
from flask_migrate import Migrate
import json
import os

# 使用Flask类创建一个app对象
app = Flask(__name__)
HOSTNAME = '123.56.182.199'
PORT = 3306
USERNAME = 'root'
PASSWORD = '123456'
DATABASE = 'flask_test'
app.config[
    'SQLALCHEMY_DATABASE_URI'] = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8mb4'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS']=True
db = SQLAlchemy(app)
migrate = Migrate(app, db)


# ORM模型映射三步曲：
# 1. 在终端中执行命令：flask db init  这步只需要执行一次，会在磁盘文件夹下生成一个migrations的文件夹
# 2. 在终端中执行命令：flask db migrate 识别ORM模型的改变，生成迁移脚本
# 3. 在终端中执行命令：flask db upgrade 运行迁移脚本，将各种表结构的变化同步到数据库中

# user = User(username="张三",password='123456')
# with app.app_context():
#     db.create_all()  #创建表

class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100))


class Article(db.Model):
    __tablename__ = 'article'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    # 添加作者的外键
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    # author = db.relationship('User', back_populates="articles") 这个需要两边的类双向绑定
    author = db.relationship('User', backref='articles')


# 创建一个路由和视图函数的映射，'/'代表跟路由
@app.route('/')
def hello_world():  # put application's code here
    return 'Hello World!'


@app.route('/user/add')
def add_user():
    user = User(username='张三', password='123456')
    db.session.add(user)
    db.session.commit()
    return "用户创建成功"


@app.route('/user/query')
def query_user():
    user = User.query.get(1)  # 根据主键查找
    print(user.id, user.username, user.password)
    users = User.query.filter_by(username='张三')
    for user in users:
        print(user.id, user.username, user.password)
    return "数据查找成功"


@app.route('/user/update')
def update_user():
    user = User.query.filter_by(username='张三').first()
    user.password = '654321'
    db.session.commit()
    return "数据修改成功"


@app.route('/user/delete')
def delete_user():
    user = User.query.filter_by(username='张三').first()
    db.session.delete(user)
    db.session.commit()
    return "数据删除成功"


@app.route('/article/add')
def add_article():
    article = Article(title='Flask基础', content='Flask是一个轻便但功能强大的Web框架')
    article.author = User.query.get(1)
    db.session.add(article)
    db.session.commit()
    return "文章创建成功"


@app.route('/article/query')
def query_article():
    user = User.query.get(1)  # 根据主键查找
    users = User.query.filter_by(username='张三')
    for article in user.articles:
        print(article.title)
    return "数据查找成功"


@app.route('/register/<int:id>')
def register(id):
    print(id)
    return f"你注册的id是{id}"

# http://IP:PORT/book/list/?name=张三&page=2
# 或者 http://IP:PORT/book/list?name=张三&page=2
@app.route('/book/list/', methods=['GET','POST'])
def book_list():
    if request.method == 'GET':
        page = request.args.get('page', default=1, type=int)
        name = request.args.get('name', default='', type=str)
        return f"您获取的是第{page}页的图书列表,作者是{name}"
    else:
        print('POST')
        data = request.get_data().decode('utf-8')
        print("here",data)
        data1 = json.loads(data)
        print(type(data1))  # <class 'dict'>
        print(data1['name'])  # {'id': 1, 'name': 'Alice'}
        # return json.dumps(data) #json.dumps(data) 将汉字转为unicode编码的坑，
        return json.dumps(data, ensure_ascii=False)

@app.route('/download/<filename>',methods=['GET'])
def download_file(filename):
    directory = os.getcwd()
    return send_from_directory(directory,filename,as_attachment=True)


if __name__ == '__main__':
    app.run()
